﻿//======================================================================
//  SQL注入攻击的安全防范方法类
//
// 修改人：郭瑞山
// 修改时间：2011-03-04
// 修改目的：新增添加SQL注释方法
//
//====================================================================== 

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

namespace Common
{
    /// <summary>
    /// SQL注入攻击的安全防范方法类
    /// </summary>
    public sealed class SQL
    {
        private const string StrKeyWord = @"select|insert|delete|from|count(|drop table|update|truncate|asc(|mid(|char(|xp_cmdshell|exec master|netlocalgroup administrators|:|net user|""|or|and";
        private const string StrRegex = @"[-|;|,|/|(|)|[|]|}|{|%|@|*|!|']";

        /// <summary>
        /// 只读数据库连接
        /// </summary>
        public static readonly string CON_READ = "read";
        /// <summary>
        /// 可写数据库连接
        /// </summary>
        public static readonly string CON_WRITE = "write";

        /// <summary>
        /// 另一种检查字符串是否包含SQL关键字的方法
        /// </summary>
        /// <param name="contents"></param>
        /// <returns>true:含有非法字符，false:被测字符串是个安全的字符串</returns>
        public static bool HasKeywords(string contents)
        {
            bool bReturnValue = false;
            if (contents.Length > 0)
            {
                string sLowerStr = contents.ToLower();
                string sRxStr = @"(\sand\s)|(\sand\s)|(\slike\s)|(select\s)|(insert\s)|(delete\s)|(update\s[\s\S].*\sset)|(create\s)|(\stable)|(<[iframe|/iframe|script|/script])|(')|(\sexec)|(declare)|(\struncate)|(\smaster)|(\sbackup)|(\smid)|(\scount)|(cast)|(%)|(\sadd\s)|(\salter\s)|(\sdrop\s)|(\sfrom\s)|(\struncate\s)|(\sxp_cmdshell\s)";
                Regex sRx = new Regex(sRxStr);
                bReturnValue = sRx.IsMatch(sLowerStr, 0);
            }
            return bReturnValue;
        }

        #region SQL注释
        /// <summary>
        /// 添加SQL注释
        /// </summary>
        /// <param name="flat">SQL语句所在平台名称:默认是TCAdmin</param>
        /// <param name="author">SQL语句的创建人</param>
        /// <param name="functionDes">SQL语句的功能描述（用途）</param>
        /// <param name="file">SQL语句所在文件</param>
        /// <param name="method">SQL所在方法名称</param>
        /// <returns>SQL语句的注释</returns>
        public static string AppendComment( string flat, string author, string functionDes, string file, string method )
        {
            if ( flat == string.Empty )

                flat = "TCAdmin";
            return string.Format( " --Flat:{0}/Author:{1}/For:{2}/File:{3}/Fun:{4}", flat, author, functionDes, file, method );
        }
        #endregion
    }
}
